
/*
'===============================================================================
'  HNG.Portal.BL.TblThanhVienManager
'===============================================================================
*/

using System;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using HNG.Portal.BLL;
using HNG.Portal.DAL;
using HNG.Portal.DAL.EntityClasses;
using HNG.Portal.DAL.FactoryClasses;
using HNG.Portal.DAL.HelperClasses;
using SD.LLBLGen.Pro.ORMSupportClasses;

namespace HNG.Portal.BLL
{
    public class TblThanhVienManager : TblThanhVienManagerBase
    {
        SqlConnection Conn = new SqlConnection(HNG.LIB.Common.Connection.strConn);
        /// <summary>
        /// Purpose: Class constructor.
        /// </summary>
        public TblThanhVienManager()
        {
            // Nothing for now.
        }
        public DataTable SelectCommon(TblThanhVienEntity _TblThanhVienEntity)
        {
            SqlConnection Conn = new SqlConnection(HNG.LIB.Common.Connection.strConn);
            System.Text.StringBuilder sbSql = new System.Text.StringBuilder();
            try
            {
                sbSql.Append("WITH tblTemp AS").Append("\n");
                sbSql.Append("(").Append("\n");
                sbSql.Append("SELECT ROW_NUMBER() OVER(ORDER BY ");
                sbSql.AppendFormat("ch.{0} ", string.IsNullOrEmpty(_TblThanhVienEntity.SortExpression) ? "[ID]" : _TblThanhVienEntity.SortExpression);
                sbSql.Append(string.IsNullOrEmpty(_TblThanhVienEntity.SortDirection) ? " DESC" : _TblThanhVienEntity.SortDirection);
                sbSql.Append(") AS RowNum,").Append("\n");
                sbSql.Append("  ch.[Id],").Append("\n");
                sbSql.Append("  ch.[GroupID],").Append("\n");
                sbSql.Append("  ch.[TenTruyCap],").Append("\n");
                sbSql.Append("  ch.[MatKhau],").Append("\n");
                sbSql.Append("  ch.[DienThoai],").Append("\n");
                sbSql.Append("  ch.[GioiTinh],").Append("\n");
                sbSql.Append("  ch.[DiaChi],").Append("\n");
                sbSql.Append("  gr.[TenNhom],").Append("\n");
                sbSql.Append("  ch.[NgayTao],").Append("\n");
                sbSql.Append("  ch.[Email],").Append("\n");
                sbSql.Append("  ch.[TrangThai]").Append("\n");
                sbSql.Append("  FROM").Append("\n");
                sbSql.Append("  tblThanhVien ch").Append("\n");
                sbSql.AppendFormat("  LEFT JOIN tblGroup gr").Append("\n");
                sbSql.Append("  ON gr.ID = ch.GroupID").Append("\n");
                sbSql.Append("  WHERE").Append("\n");
                sbSql.Append("  1 = 1").Append("\n");

                if (!string.IsNullOrEmpty(_TblThanhVienEntity.ID))
                    sbSql.AppendFormat("  AND ch.[ID] LIKE {0}", _TblThanhVienEntity.ID).Append("\n");

                if (!string.IsNullOrEmpty(_TblThanhVienEntity.Condition))
                {
                    sbSql.AppendFormat("  AND (ch.[ID] LIKE N'%' + {0} + '%'", _TblThanhVienEntity.Condition).Append("\n");
                    sbSql.AppendFormat("  OR ch.[GroupID] LIKE N'%' + {0} + '%'", _TblThanhVienEntity.Condition).Append("\n");
                    sbSql.AppendFormat("  OR ch.[TenTruyCap] LIKE N'%' + {0} + '%'", _TblThanhVienEntity.Condition).Append("\n");
                    sbSql.AppendFormat("  OR ch.[MatKhau] LIKE N'%' + {0} + '%'", _TblThanhVienEntity.Condition).Append("\n");
                    sbSql.AppendFormat("  OR ch.[DienThoai] LIKE N'%' + {0} + '%'", _TblThanhVienEntity.Condition).Append("\n");
                    sbSql.AppendFormat("  OR ch.[GioiTinh] LIKE N'%' + {0} + '%'", _TblThanhVienEntity.Condition).Append("\n");
                    sbSql.AppendFormat("  OR ch.[DiaChi] LIKE N'%' + {0} + '%'", _TblThanhVienEntity.Condition).Append("\n");
                    sbSql.AppendFormat("  OR ch.[NgayTao] LIKE N'%' + {0} + '%')", _TblThanhVienEntity.Condition).Append("\n");
                    sbSql.AppendFormat("  OR ch.[Email] LIKE N'%' + {0} + '%')", _TblThanhVienEntity.Condition).Append("\n");
                    sbSql.AppendFormat("  OR ch.[TrangThai] LIKE N'%' + {0} + '%')", _TblThanhVienEntity.Condition).Append("\n");
                }

                if (!string.IsNullOrEmpty(_TblThanhVienEntity.DM))
                    sbSql.AppendFormat("  AND ch.[GroupId] like {0}", _TblThanhVienEntity.DM).Append("\n");

                if (!string.IsNullOrEmpty(_TblThanhVienEntity.TrangThai.ToString()))
                    sbSql.AppendFormat("  AND ch.[TrangThai] like {0}", _TblThanhVienEntity.TrangThai).Append("\n");

                sbSql.Append(")").Append("\n");
                sbSql.Append("SELECT *, (SELECT Max(RowNum) FROM tblTemp) TotalItems FROM tblTemp").Append("\n");
                sbSql.AppendFormat("WHERE RowNum BETWEEN {0} AND {1}", _TblThanhVienEntity.StartIndex, _TblThanhVienEntity.EndIndex).Append("\n");
            }
            catch { }
            return AE.SqlHelper.ExecuteDataTable(Conn, CommandType.Text, sbSql.ToString());
        }

        public DataTable Get_Bykeysearch(string keysearch, string columnName)
        {
            SqlConnection Conn = new SqlConnection(HNG.LIB.Common.Connection.strConn);
            string strQuery = "select * from tblThanhVien ";
            if (columnName != "")
                strQuery += " where " + columnName + " like N'%" + keysearch + "%'" + " Order by ID desc";
            else
                strQuery += " where " + TblThanhVienFields.TenTruyCap.Name + " like N'%" + keysearch + "%'" + " Order by ID desc";
            return AE.SqlHelper.ExecuteDataTable(Conn, CommandType.Text, strQuery);
        }

        public EntityCollection SelectByUser_Pass(string UserName, string Pass)
        {
            EntityCollection _UserCollection = new EntityCollection(new TblThanhVienEntityFactory());
            RelationPredicateBucket filter = new RelationPredicateBucket();

            IPredicateExpression _PredicateExpression = new PredicateExpression();
            _PredicateExpression.Add(TblThanhVienFields.TenTruyCap == UserName);
            _PredicateExpression.AddWithAnd(TblThanhVienFields.MatKhau == Pass);
            _PredicateExpression.AddWithAnd(TblThanhVienFields.TrangThai == 1);
            filter.PredicateExpression.Add(_PredicateExpression);

            using (DataAccessAdapterBase adapter = (new DataAccessAdapterFactory()).CreateAdapter())
            {
                adapter.FetchEntityCollection(_UserCollection, filter, 0, null);
            }
            return _UserCollection;
        }

        public DataTable StoreToDataTable(string UserName, string Pass)
        {
            SqlConnection Conn = new SqlConnection(HNG.LIB.Common.Connection.strConn);
            SqlParameter[] _SqlParameter = new SqlParameter[2];
            _SqlParameter[0] = new SqlParameter("@TenDangNhap", UserName);
            _SqlParameter[1] = new SqlParameter("@MatKhau", Pass);
            return AE.SqlHelper.ExecuteDataTable(Conn, "sp_KiemTraDangNhap", _SqlParameter);
        }

        public DataTable GetIDByUserName(string UserName)
        {
            SqlConnection Conn = new SqlConnection(HNG.LIB.Common.Connection.strConn);
            SqlParameter[] _SqlParameter = new SqlParameter[1];
            _SqlParameter[0] = new SqlParameter("@TenDangNhap", UserName);
            return AE.SqlHelper.ExecuteDataTable(Conn, "sp_GetIdUserByName", _SqlParameter);
        }

        public DataTable be_selectToGridView(TblThanhVienEntity _TblThanhVienEntity)
        {
            System.Text.StringBuilder sbSql = new System.Text.StringBuilder();
            SqlParameter[] _SqlParameter = new SqlParameter[3];
            try
            {
                sbSql.Append("WITH tblTemp AS").Append("\n");
                sbSql.Append("(").Append("\n");
                sbSql.Append("SELECT ROW_NUMBER() OVER(ORDER BY ");
                sbSql.AppendFormat("ch.{0} ", string.IsNullOrEmpty(_TblThanhVienEntity.SortExpression) ? "[ID]" : _TblThanhVienEntity.SortExpression);
                sbSql.Append(string.IsNullOrEmpty(_TblThanhVienEntity.SortDirection) ? " DESC" : _TblThanhVienEntity.SortDirection);
                sbSql.Append(") AS RowNum,").Append("\n");
                sbSql.Append("  ch.[Id],").Append("\n");
                sbSql.Append("  ch.[TenTruyCap],").Append("\n");
                sbSql.Append("  ch.[FullName],").Append("\n");
                sbSql.Append("  ch.[Email]").Append("\n");
                sbSql.Append("  FROM").Append("\n");
                sbSql.Append("  tblThanhVien ch").Append("\n");
                sbSql.Append("  WHERE").Append("\n");
                sbSql.Append("  1 = 1").Append("\n");

                if (!string.IsNullOrEmpty(_TblThanhVienEntity.ID))
                {
                    _SqlParameter[0] = new SqlParameter("@ID", _TblThanhVienEntity.ID);
                    sbSql.Append("  AND ch.[ID] LIKE @ID").Append("\n");

                }
                string strCon = _TblThanhVienEntity.Condition;
                if (!string.IsNullOrEmpty(_TblThanhVienEntity.Condition))
                {
                    sbSql.AppendFormat("  AND (ch.[TenTruyCap] LIKE '%{0}%'", strCon).Append("\n");
                    sbSql.AppendFormat("  OR ch.[FullName] LIKE '%' + {0} + '%'", _TblThanhVienEntity.Condition).Append("\n");
                    sbSql.AppendFormat("  OR ch.[DienThoai] LIKE '%' + {0} + '%'", _TblThanhVienEntity.Condition).Append("\n");
                    sbSql.AppendFormat("  OR ch.[Email] LIKE '%' + {0} + '%'", _TblThanhVienEntity.Condition).Append("\n");
                    sbSql.AppendFormat("  OR ch.[DiaChi] LIKE '%' + {0} + '%'", _TblThanhVienEntity.Condition).Append("\n");

                    sbSql.AppendFormat("  OR ch.[TenTruyCap] LIKE N'%{0}%'", strCon).Append("\n");
                    sbSql.AppendFormat("  OR ch.[FullName] LIKE N'%' + {0} + '%'", _TblThanhVienEntity.Condition).Append("\n");
                    sbSql.AppendFormat("  OR ch.[DienThoai] LIKE N'%' + {0} + '%'", _TblThanhVienEntity.Condition).Append("\n");
                    sbSql.AppendFormat("  OR ch.[Email] LIKE N'%' + {0} + '%'", _TblThanhVienEntity.Condition).Append("\n");
                    sbSql.AppendFormat("  OR ch.[DiaChi] LIKE N'%' + {0} + '%'", _TblThanhVienEntity.Condition).Append("\n");

                    sbSql.Append(")").Append("\n");
                }

                if (!string.IsNullOrEmpty(_TblThanhVienEntity.Status))
                {
                    _SqlParameter[2] = new SqlParameter("@TrangThai", _TblThanhVienEntity.TrangThai);
                    sbSql.Append("  AND ch.[TrangThai] LIKE @TrangThai").Append("\n");
                }

                sbSql.Append(")").Append("\n");
                sbSql.Append("SELECT *, (SELECT Max(RowNum) FROM tblTemp) TotalItems FROM tblTemp").Append("\n");
                sbSql.AppendFormat("WHERE RowNum BETWEEN {0} AND {1}", _TblThanhVienEntity.StartIndex, _TblThanhVienEntity.EndIndex).Append("\n");
            }
            catch { }
            return AE.SqlHelper.ExecuteDataTable(Conn, CommandType.Text, sbSql.ToString(), _SqlParameter);
        }
    }
}
